Present invention embodiments relate to systems, methods and computer program products for supplementing a traditional index access plan in a database management system with a secondary, or helper, scan or scans. In particular, the present invention embodiments relate to systems, methods and computer program products for sharing information from one or more helper scans with a main index access method in order to improve filtering for the main index.
SQL performance of OnLine Transaction Processing (OLTP) queries in a traditional row store database is significantly influenced by coded WHERE clause predicates and available indexing. When an index that supports filtering on all available predicates is missing and/or multiple index choices exist, a single sub-optimal index may be chosen to support a required order for querying, or multiple index access may be chosen in which an intersection of filtering from each index may produce a result. With multiple index access, each index may provide an individual Record Identifier (RID) list and an intersection of the RID lists reduces a total number of entries. However, index key order is not preserved when RID lists are intersected using sorting or a hash intersection of the RID lists and any required order for a query must be sorted. Typically, multiple index access is advantageous only when available indexes provide matching index access such that each index access qualifies a subset of rows. Matching index access refers to an ability to use available WHERE clause predicates to limit a search range. Indexed predicates that are non-matching are applied for index screening. In other words, non-matching indexed predicates limit qualified index rows, but do not limit the number of index keys processed.
Sub-optimal index choices are demonstrated in the following SQL example, in which no index exists that can support both matching index access and sort avoidance to support an ORDER BY clause. In this example, no suitable indexes exist for a matching multi-index access.